package com.fejal.dao;


import java.util.List;
import com.fejal.bean.Historico;


public class HistoricoDAOHibernate implements HistoricoDAO {

	HibernateDAOFactory hibernateDAOFactory = null;
	HibernateDAO<Historico> hibernateDAO = null;

	public HistoricoDAOHibernate() {
		hibernateDAOFactory = new HibernateDAOFactory();
		hibernateDAO = hibernateDAOFactory.getHistoricoDao();
	}

	@Override
	public boolean inserir(Historico historico) throws Exception {

		boolean ok = false;

		try {
			hibernateDAO.add(historico);
			ok = true;

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		}

		return ok;
	}

	@Override
	public boolean alterar(Historico historico) throws Exception{

		boolean ok = false;
	
		
		try {
			hibernateDAO.update(historico);
			ok = true;
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} 

		return ok;
	}

	@Override
	public boolean excluir(Historico historico) throws Exception{

		boolean ok = false;

		try {
			hibernateDAO.remove(historico);
			ok = true;

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} 

		return ok;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Historico> getSelecionar(String descricao) {
		String 	sql = "FROM Historico WHERE codigoHistorico <> 0 ";

		if (!(descricao.isEmpty())) {
			sql += "AND descricao like '" + descricao + "%' ";  
		}
		
		sql += "ORDER BY codigoHistorico";		
 
		List<Historico> lista = hibernateDAO.getSession().createQuery(sql).list();
		return lista;
	}

	@Override
	public Historico getHistorico(int codigoHistorico) {
		
		Historico historico = hibernateDAO.get(codigoHistorico);
		return historico;
	}

	@Override
	public List<Historico> getListTodos() {
		
		List<Historico> list = hibernateDAO.listAll();
		return list;
	}

	
	@SuppressWarnings("unchecked")
	@Override
	public boolean isExiste(Historico historico) {
		// SQL para verificar se a Historico existe
		String 	sql = 	"FROM Historico " +
				"WHERE codigoHistorico = "+historico.getCodigoHistorico();

		List<Historico> lista = hibernateDAO.getSession().createQuery(sql).list();
		if(lista.size()<1){
			return false;
		} else {
			return true;
		}
	}
	@SuppressWarnings("unchecked")
	@Override
	public List<Historico> getSelecionar(int codigoTurma, int codigoDisciplina, int ano, int semestre) {

		if ( (codigoTurma == 0) || (codigoDisciplina == 0) || (ano == 0) || (semestre == 0) ){
			return null;
		}
		
		String 	sql = "";
		sql += " SELECT historico as historico ";
		sql += " FROM Aluno as aluno ";
		sql += " INNER JOIN aluno.turmaOfertaDisciplinas as turmaOFertaDisciplina ";
		sql += " INNER JOIN aluno.historico as historico ";
		sql += " WHERE turmaOFertaDisciplina.turmaOferta.turma.codigoTurma = "+codigoTurma;
		sql += "   AND historico.disciplina.codigoDisciplina = "+codigoDisciplina;
		sql += "   AND turmaOFertaDisciplina.turmaOferta.ano = "+ano;
		sql += "   AND turmaOFertaDisciplina.turmaOferta.semestre = "+semestre;
		sql += "   AND turmaOFertaDisciplina.disciplina.codigoDisciplina = "+codigoDisciplina; 
		sql += "ORDER BY numeroCaderneta";		
 
		List<Historico> lista = hibernateDAO.getSession().createQuery(sql).list();
		return lista;
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Historico> getSelecionarAlfa(int codigoTurma, int codigoDisciplina, int ano, int semestre) {

		if ( (codigoTurma == 0) || (codigoDisciplina == 0) || (ano == 0) || (semestre == 0) ){
			return null;
		}
		
		String 	sql = "";
		sql += " SELECT historico as historico";
		sql += " FROM Aluno as aluno ";
		sql += " INNER JOIN aluno.turmaOfertaDisciplinas as turmaOFertaDisciplina ";
		sql += " INNER JOIN aluno.historico as historico ";
		sql += " WHERE turmaOFertaDisciplina.turmaOferta.turma.codigoTurma = "+codigoTurma;
		sql += "   AND historico.disciplina.codigoDisciplina = "+codigoDisciplina;
		sql += "   AND turmaOFertaDisciplina.turmaOferta.ano = "+ano;
		sql += "   AND turmaOFertaDisciplina.turmaOferta.semestre = "+semestre;
		sql += "   AND turmaOFertaDisciplina.disciplina.codigoDisciplina = "+codigoDisciplina; 
		sql += " ORDER BY aluno.pessoa.nome";		
 
		List<Historico> lista = hibernateDAO.getSession().createQuery(sql).list();
		return lista;
	}
	
	@Override
	public int getMaximoNumeroCaderneta(int codigoTurma, int codigoDisciplina, int ano, int semestre) {
		if ( (codigoTurma == 0) || (codigoDisciplina == 0) || (ano == 0) || (semestre == 0) ){
			return 0;
		}
		
		String 	sql = "";
		sql += " SELECT MAX(historico.numeroCaderneta) as maxNumeroCaderneta";
		sql += " FROM Aluno as aluno ";
		sql += " INNER JOIN aluno.turmaOfertaDisciplinas as turmaOFertaDisciplina ";
		sql += " INNER JOIN aluno.historico as historico ";
		sql += " WHERE turmaOFertaDisciplina.turmaOferta.turma.codigoTurma = "+codigoTurma;
		sql += "   AND historico.disciplina.codigoDisciplina = "+codigoDisciplina;
		sql += "   AND turmaOFertaDisciplina.turmaOferta.ano = "+ano;
		sql += "   AND turmaOFertaDisciplina.turmaOferta.semestre = "+semestre;
		sql += "   AND turmaOFertaDisciplina.disciplina.codigoDisciplina = "+codigoDisciplina; 
		sql += " ORDER BY aluno.pessoa.nome";		
 
		return (Integer) hibernateDAO.getSession().createQuery(sql).uniqueResult();
		
	}
	
}
